﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Emoney.DataAccess;
using LiuYan.Base;
using LiuYan.Entity;


namespace LiuYan.Service
{
   public class SortService
    {

        public int Edit(TB_Sort entity)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@sort",SqlDbType.VarChar,50), 
                new SqlParameter("@order",SqlDbType.Int),
                 new SqlParameter("@des",SqlDbType.VarChar,50),
                  new SqlParameter("@img",SqlDbType.VarChar,50) 
            };
            parameters[0].Value = entity.Id;
            parameters[1].Value = entity.Name;
            parameters[2].Value = entity.Order;
            parameters[3].Value = entity.Des;
            parameters[4].Value = entity.Img;
            string sql = entity.Id == 0 ?
                " INSERT INTO TB_Sort(Name,[Order],Des,Img)VALUES(@sort,@order,@des,@img) " :
                 "UPDATE TB_Sort SET Name=@sort,[order]=@order,des=@des,img=@img WHERE Id=@id";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql, parameters);
        }

        public DataTable GetList()
        {
            string sql = "SELECT * FROM dbo.TB_Sort order by [order] asc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, null).Tables[0];
        }

       /// <summary>
       /// 查询已有商品的分类
       /// </summary>
       /// <returns></returns>
       public DataTable GetDList()
       {
           string sql = "select distinct a.Id,a.Name from dbo.TB_Sort as a ,dbo.TB_Goods as b where a.id=b.sort and b.del=0 and b.xj=0 and b.discountprice>0";
           return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, null).Tables[0];
       }

       public int Del(int id)
       {
           string sql = "select count(*) as count from TB_Goods where sort="+id;//查询商品表中是否存在该分类的商品
           if (Convert.ToInt32(SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql)) > 0)
           {
               return -1;
           }
           sql = "delete TB_Sort where id="+id;
           return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
       }

       /// <summary>
       /// 获取分类名称
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public string GetSortName(int id)
       {
           string sql = "select name from TB_Sort where id="+id;
           return SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql).ToString();
       }
    }
}
